<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use DB;


set_time_limit(0); //解除PHP脚本时间30s限制
ini_set('memory_limit','128M');//修改内存值
class RepairSfzCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'repair:sfz';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'repair user sfz to repair_user_cert_ocrs';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $toDay = '2019-04-12 00:00:00';
        $toDay = strtotime($toDay);
        $two_dawn = '2019-04-12 23:59:59';
        $two_dawn = strtotime($two_dawn);
        if (time() >= $toDay && time() <= $two_dawn){
            //修复之前的身份证
            $this->repairSfzOne();
        }else{
            //修复T-1天的身份证
            $this->repairSfzDaily();
        }
        //修复用户的身份证url地址
        $this->repairUrl();
    }

    /**
     * 修复用户身份证，只跑一次
     * @param $request
     * @param Closure $next
     * @return mixed
     * @throws GeneralException
     */
    public function repairSfzOne()
    {
        //每次取出的数据
        $num = 12000;
        //每次从第几条取
        $start = 0;
        for ($i = 0; $i < 150; $i++){
            $filter_arr = [];
            $data = DB::select('select user_id,num, id_card_front,id_card_back,hand_cert,name,domicile_address,issuing_authority,
issue_gov_code,valid_date_begin,valid_date_end,upload, is_valid, created_at, updated_at from user_cert_ocrs
            as a join  (select max(id) mid from user_cert_ocrs
          GROUP BY num order by id) as b on a.id = b.mid' .' '. 'limit' .' ' . $num .' '. 'offset' .' '. $start);
            foreach ($data as $v){
                $filter_arr[] = (array)$v;
            }
            $chunk_arr = array_chunk($filter_arr, 100);
            foreach ($chunk_arr as $v){
                DB::table('repair_user_cert_ocrs')
                    ->insert($v);
            }
            $start += 12000;
        }
    }

    /**
     * 修复用户身份证，T-1天
     * @param $request
     * @param Closure $next
     * @return mixed
     * @throws GeneralException
     */
    public function repairSfzDaily()
    {
        $beginTime = date("Y-m-d",strtotime("-1 day"))." 00:00:00";
        $endTime = date("Y-m-d",strtotime("-1 day"))." 23:59:59";
        $list = DB::table('user_cert_ocrs')
            ->where('created_at','>',$beginTime)
            ->where('created_at','<',$endTime)
            ->orderByDesc('id')
            ->pluck('num','id')
            ->toArray();
        //所有用户ID集合
        $new_ids = DB::table('repair_user_cert_ocrs')
            ->pluck('num','id')
            ->toArray();
        $ids = array_unique($list);
        $res_array = array_diff($ids,$new_ids);
        if(!empty($res_array))
        {

            $arr_info = [];
            $old_info = DB::table('user_cert_ocrs')
                ->select(['id','user_id','id_card_front','id_card_back','hand_cert','name','num','domicile_address','issuing_authority'
                        ,'issue_gov_code','valid_date_begin','valid_date_end','upload','is_valid','created_at','updated_at']
                )
                ->where('created_at','>',$beginTime)
                ->where('created_at','<',$endTime)
                ->whereIn('id',array_keys($res_array))
                ->get();
            foreach ($old_info as  $key=>$v) {
                $arr_info[$key] = (array)$v;
            }

            $chunk = array_chunk($arr_info,100);
            foreach ($chunk as $v) {
                DB::table('repair_user_cert_ocrs')->insert($v);
            }
        }
    }


    /**
     * 修复用户身份证url，每天都跑
     * @param $request
     * @param Closure $next
     * @return mixed
     * @throws GeneralException
     */
    public function repairUrl()
    {
        $sfz_url_num = 5000;
        $sfz_url_start = 0;
        for ($j = 0; $j < 150; $j++){
            $damage_sfz_urls = DB::select("select id, id_card_front, id_card_back, hand_cert from repair_user_cert_ocrs 
where id_card_front like 'fron%' or id_card_back like 'fron%' or hand_cert like 'fron%'" . ' ' . 'limit' . ' ' . $sfz_url_num .' ' . 'offset' . ' ' . $sfz_url_start);
            $perfect_sfz_urls = [];
            if (empty($damage_sfz_urls)) return ;
            foreach ($damage_sfz_urls as $damage_sfz_url){
                $damage_sfz_url = (array)$damage_sfz_url;
                $damage_sfz_url['id_card_front'] = getOssUrl($damage_sfz_url['id_card_front'], 3153600000);
                $damage_sfz_url['id_card_back'] = getOssUrl($damage_sfz_url['id_card_back'], 3153600000);
                $damage_sfz_url['hand_cert'] = getOssUrl($damage_sfz_url['hand_cert'], 3153600000);
                $perfect_sfz_urls[] = $damage_sfz_url;
            }
            $perfect_sfz_urls = array_chunk($perfect_sfz_urls, 100);
            foreach ($perfect_sfz_urls as $perfect_sfz_url){
                foreach ($perfect_sfz_url as $v){
                    DB::table('repair_user_cert_ocrs')
                        ->where('id', $v['id'])
                        ->update($v);
                }
            }

            $sfz_url_num += 5000;
        }
    }
}